package com.lanvideo.dao;

import com.lanvideo.entity.ExamRecord;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 考试记录数据访问层
 */
@Repository
public class ExamRecordDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 添加考试记录
     */
    public int addExamRecord(ExamRecord record) {
        String sql = "INSERT INTO exam_record (exam_id, student_id, score, total_questions, exam_time) VALUES (?, ?, ?, ?, ?)";
        return jdbcTemplate.update(sql, record.getExamId(), record.getStudentId(), record.getScore(), 
                record.getTotalQuestions(), record.getExamTime());
    }

    /**
     * 获取学生的考试记录
     */
    public List<ExamRecord> getStudentExamRecords(Integer studentId) {
        String sql = "SELECT er.*, u.name as student_name, e.title as exam_title " +
                "FROM exam_record er " +
                "LEFT JOIN user u ON er.student_id = u.id " +
                "LEFT JOIN exam e ON er.exam_id = e.id " +
                "WHERE er.student_id = ? " +
                "ORDER BY er.exam_time DESC";
        
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<ExamRecord>() {
            @Override
            public ExamRecord mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
                ExamRecord record = new ExamRecord();
                record.setId(rs.getInt("id"));
                record.setExamId(rs.getInt("exam_id"));
                record.setStudentId(rs.getInt("student_id"));
                record.setStudentName(rs.getString("student_name"));
                record.setScore(rs.getInt("score"));
                record.setTotalQuestions(rs.getInt("total_questions"));
                record.setExamTime(rs.getTimestamp("exam_time") != null ? 
                    rs.getTimestamp("exam_time").toLocalDateTime() : null);
                // 设置考试标题
                record.setExamTitle(rs.getString("exam_title"));
                return record;
            }
        }, studentId);
    }

    /**
     * 获取考试的所有记录
     */
    public List<ExamRecord> getExamRecords(Integer examId) {
        String sql = "SELECT er.*, u.name as student_name " +
                "FROM exam_record er " +
                "LEFT JOIN user u ON er.student_id = u.id " +
                "WHERE er.exam_id = ? " +
                "ORDER BY er.exam_time DESC";
        
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<ExamRecord>() {
            @Override
            public ExamRecord mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
                ExamRecord record = new ExamRecord();
                record.setId(rs.getInt("id"));
                record.setExamId(rs.getInt("exam_id"));
                record.setStudentId(rs.getInt("student_id"));
                record.setStudentName(rs.getString("student_name"));
                record.setScore(rs.getInt("score"));
                record.setTotalQuestions(rs.getInt("total_questions"));
                record.setExamTime(rs.getTimestamp("exam_time") != null ? 
                    rs.getTimestamp("exam_time").toLocalDateTime() : null);
                return record;
            }
        }, examId);
    }

    /**
     * 获取学生的特定考试记录
     */
    public ExamRecord getStudentExamRecord(Integer examId, Integer studentId) {
        String sql = "SELECT er.*, u.name as student_name " +
                "FROM exam_record er " +
                "LEFT JOIN user u ON er.student_id = u.id " +
                "WHERE er.exam_id = ? AND er.student_id = ?";
        
        List<ExamRecord> records = jdbcTemplate.query(sql, new BeanPropertyRowMapper<ExamRecord>() {
            @Override
            public ExamRecord mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
                ExamRecord record = new ExamRecord();
                record.setId(rs.getInt("id"));
                record.setExamId(rs.getInt("exam_id"));
                record.setStudentId(rs.getInt("student_id"));
                record.setStudentName(rs.getString("student_name"));
                record.setScore(rs.getInt("score"));
                record.setTotalQuestions(rs.getInt("total_questions"));
                record.setExamTime(rs.getTimestamp("exam_time") != null ? 
                    rs.getTimestamp("exam_time").toLocalDateTime() : null);
                return record;
            }
        }, examId, studentId);
        
        return records.isEmpty() ? null : records.get(0);
    }

    /**
     * 删除考试记录
     */
    public int deleteExamRecord(Integer id) {
        String sql = "DELETE FROM exam_record WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    /**
     * 获取考试统计信息
     */
    public ExamRecord getExamStats(Integer examId) {
        String sql = "SELECT " +
                "COUNT(*) as total_students, " +
                "AVG(score) as avg_score, " +
                "MAX(score) as max_score, " +
                "MIN(score) as min_score " +
                "FROM exam_record " +
                "WHERE exam_id = ?";
        
        List<ExamRecord> stats = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ExamRecord.class), examId);
        return stats.isEmpty() ? null : stats.get(0);
    }
}